from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `group` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL UNIQUE COMMENT '用户组名称',
    `level` INT NOT NULL  COMMENT '权限等级'
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `user` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(32) NOT NULL UNIQUE COMMENT '用户名',
    `password` VARCHAR(64) NOT NULL  COMMENT '密码',
    `email` VARCHAR(64) NOT NULL UNIQUE COMMENT '邮箱',
    `is_active` BOOL NOT NULL  COMMENT '账户是否激活' DEFAULT 1,
    `created_at` DATETIME(6) NOT NULL  COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP(6),
    `crawl_count` INT NOT NULL  COMMENT '爬取次数' DEFAULT 0,
    `groups_id` INT NOT NULL,
    CONSTRAINT `fk_user_group_561487ac` FOREIGN KEY (`groups_id`) REFERENCES `group` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `bilibilivideo` (
    `aid` VARCHAR(32) NOT NULL  PRIMARY KEY COMMENT '视频aid',
    `bv_id` VARCHAR(32) NOT NULL  COMMENT '视频bvid',
    `title` VARCHAR(255) NOT NULL  COMMENT '视频标题',
    `desc` LONGTEXT NOT NULL  COMMENT '视频简介',
    `owner` VARCHAR(32) NOT NULL  COMMENT '视频作者',
    `views` INT NOT NULL  COMMENT '观看次数' DEFAULT 0,
    `likes` INT NOT NULL  COMMENT '点赞数' DEFAULT 0,
    `comments_count` INT NOT NULL  COMMENT '评论数' DEFAULT 0,
    `user_id` INT NOT NULL,
    CONSTRAINT `fk_bilibili_user_e051d208` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `bilibilicomment` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `comment_text` LONGTEXT NOT NULL  COMMENT '评论内容',
    `comment_time` DATETIME(6) NOT NULL  COMMENT '评论时间',
    `user_id` VARCHAR(32) NOT NULL  COMMENT '用户ID',
    `user_nickname` VARCHAR(32) NOT NULL  COMMENT '用户昵称',
    `user_sex` VARCHAR(8) NOT NULL  COMMENT '用户性别',
    `sub_comment_count` INT NOT NULL  COMMENT '子评论数' DEFAULT 0,
    `likes` INT NOT NULL  COMMENT '点赞数' DEFAULT 0,
    `address` VARCHAR(32) NOT NULL  COMMENT 'IP属地',
    `emotional_value` DOUBLE NOT NULL  COMMENT '情绪值' DEFAULT 0.5,
    `video_id` VARCHAR(32) NOT NULL,
    CONSTRAINT `fk_bilibili_bilibili_91872201` FOREIGN KEY (`video_id`) REFERENCES `bilibilivideo` (`aid`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `douyinvideo` (
    `aweme_id` VARCHAR(32) NOT NULL  PRIMARY KEY COMMENT '视频ID',
    `title` VARCHAR(255) NOT NULL  COMMENT '视频标题',
    `owner` VARCHAR(32) NOT NULL  COMMENT '视频作者',
    `likes` INT NOT NULL  COMMENT '点赞数' DEFAULT 0,
    `comments_count` INT NOT NULL  COMMENT '评论数' DEFAULT 0,
    `user_id` INT NOT NULL,
    CONSTRAINT `fk_douyinvi_user_b4bfa632` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `douyincomment` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `comment_text` LONGTEXT NOT NULL  COMMENT '评论内容',
    `comment_time` DATETIME(6) NOT NULL  COMMENT '评论时间',
    `user_id` VARCHAR(32) NOT NULL  COMMENT '用户ID',
    `user_nickname` VARCHAR(32) NOT NULL  COMMENT '用户昵称',
    `user_sex` VARCHAR(8) NOT NULL  COMMENT '用户性别',
    `sub_comment_count` INT NOT NULL  COMMENT '子评论数' DEFAULT 0,
    `likes` INT NOT NULL  COMMENT '点赞数' DEFAULT 0,
    `address` VARCHAR(32) NOT NULL  COMMENT 'IP属地',
    `emotional_value` DOUBLE NOT NULL  COMMENT '情绪值' DEFAULT 0.5,
    `video_id` VARCHAR(32) NOT NULL,
    CONSTRAINT `fk_douyinco_douyinvi_d95e1f1e` FOREIGN KEY (`video_id`) REFERENCES `douyinvideo` (`aweme_id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `systemlog` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `request_time` DATETIME(6) NOT NULL  COMMENT '登录时间' DEFAULT CURRENT_TIMESTAMP(6),
    `router` VARCHAR(64) NOT NULL  COMMENT '路由地址',
    `request_ip` VARCHAR(64) NOT NULL  COMMENT 'IP地址',
    `request_status` VARCHAR(16) NOT NULL  COMMENT '登录状态',
    `user_name_id` INT NOT NULL,
    CONSTRAINT `fk_systemlo_user_b99a54d0` FOREIGN KEY (`user_name_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `aerich` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `version` VARCHAR(255) NOT NULL,
    `app` VARCHAR(100) NOT NULL,
    `content` JSON NOT NULL
) CHARACTER SET utf8mb4;"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """
